IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_DistributionInspection_ProjectSummary_Isssue')
	BEGIN
		DROP  Procedure  up_Report_DistributionInspection_ProjectSummary_Isssue
	END

GO

CREATE Procedure [dbo].[up_Report_DistributionInspection_ProjectSummary_Isssue]
	@ProjectId int = NULL
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SELECT  @ProjectId = CONVERT(INT, SettingValue) FROM tb_Settings
	WHERE SettingKey like 'DistributionInspectionProjectId'
END

DECLARE @inspectionAssetResult table (id int, [name] varchar(MAX), [order] int)

INSERT INTO @inspectionAssetResult VALUES (1, 'Assets Passing Inspection', 3)
INSERT INTO @inspectionAssetResult VALUES (2, 'Assets Passing Inspection with Warning', 4)
INSERT INTO @inspectionAssetResult VALUES (3, 'Asset Rejections', 5)

SELECT
		ISNULL(SUM(Completed.AmountAssetsForResult), 0) as AmountAssets,
		results.[name] as ColumnName,
		@ProjectId as ProjectId,
		results.[order] as [Order]
	from
		@inspectionAssetResult results
	LEFT JOIN
		(
		select 
			COUNT(1) as AmountAssetsForResult,
			R.Vendor_Distribution_ResultTypeFk as ResultTypeId,
			RT.Name,
			assetResultFk = CASE  
							WHEN R.Vendor_Distribution_ResultTypeFk =  1 THEN  1
							WHEN R.Vendor_Distribution_ResultTypeFk =  2 THEN  2
							ELSE 3
						 END
		from dbo.tb_Vendor_Distribution_Poles P
		INNER JOIN  dbo.tb_Vendor_Distribution_InspectionResults R 
			ON R.Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId
		INNER JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes RT 
			ON RT.Vendor_InspVisit_XmissionStructures_ResultTypeId = R.Vendor_Distribution_ResultTypeFk
		where 
			P.ProjectFk  = @ProjectId
			and	P.DistributionType = 1
		group by R.Vendor_Distribution_ResultTypeFk, RT.Name
		) as Completed
		ON results.id = assetResultFk
	group by results.[name], results.[order]
	
UNION ALL
	select 
		COUNT(R.bElectrical) as AmountAssets,
		'Electrical Concerns',
		@ProjectId as ProjectId,
		1 as [Order]
	from tb_Vendor_Distribution_Poles P
		INNER JOIN tb_Vendor_Distribution_InspectionResults R ON R.Vendor_Distribution_PoleFK = P.Vendor_Distribution_Poles_PoleId
	where 
		R.bElectrical = 1 AND
		P.ProjectFk = @ProjectId
		and	P.DistributionType = 1
UNION ALL
	select 
		COUNT(1) as AmountAssets,
		'High Priority Rejections',
		@ProjectId as ProjectId,
		2 as [order]
	from tb_Vendor_Distribution_Poles P
		INNER JOIN tb_Vendor_Distribution_InspectionResults R ON R.Vendor_Distribution_PoleFK = P.Vendor_Distribution_Poles_PoleId
	where 
		R.Vendor_Distribution_ResultTypeFk = 6 AND
		P.ProjectFk = @ProjectId
		and	P.DistributionType = 1
Order by [order]






GO

GRANT EXEC ON up_Report_DistributionInspection_ProjectSummary_Isssue TO PPIReports

GO


